﻿Module modDB
    Public oDBConn As OleDb.OleDbConnection

    'Установить соединение с базой данных
    Public Sub ConnectDB()
        Dim DBPath As String
        If oDBConn Is Nothing Then
            DBPath = Application.StartupPath & "\Resources\base.mdb"
            'DBPath = "D:\SHARED\Прайс-лист\Resources\base.mdb"
            oDBConn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath)
            oDBConn.Open()
        End If
    End Sub


    'Закрыть подключение
    Public Sub CloseDB()
        If Not IsNothing(oDBConn) Then
            oDBConn.Close()
            oDBConn = Nothing
        End If
    End Sub



    '===================================
    '=== Работа с таблицей риэлторов ===
    '===================================

    'Проверяет номер phone в базе данных
    'Если convert=True, то нужно перед проверкой получить уменьшеный телефон
    'Возвращает: 0 - если phone - не телефон
    '            true - если phone - риэлтор
    '            false - если phone - не риэлтор
    Public Function DBEstate_isEstate(ByVal phone As Object, ByVal convert As Boolean) As Object
        If convert = True Then
            phone = getBigPhone(phone)
        End If
        phone = getSmallPhone(phone)
        If phone = False Then
            Return 0
        End If

        ConnectDB() 'подключаемся к базе данных
        Dim rc As OleDb.OleDbCommand
        rc = New OleDb.OleDbCommand("SELECT phone FROM estate_phones WHERE phone='" & phone & "'", oDBConn)
        If rc.ExecuteScalar() Then
            Return True
        Else
            Return False
        End If
    End Function


    'Добавляет телефон в таблицу риэлторов
    Public Sub DBEstate_AddEstate(ByVal phone As String)
        Try
            phone = getBigPhone(phone)
            phone = getSmallPhone(phone)
            If phone <> False Then
                ConnectDB() 'подключаемся к базе данных
                'Добавляем телефон в таблицу риэлторов
                Dim rc As OleDb.OleDbCommand
                If DBEstate_isEstate(phone, False) = False Then
                    rc = New OleDb.OleDbCommand("INSERT INTO `estate_phones` (`phone`) VALUES ('" & phone & "')", oDBConn)
                    rc.ExecuteNonQuery()
                    rc.Dispose()
                End If

                'Удаляем телефон из таблицы счетчика телефонов
                rc = New OleDb.OleDbCommand("DELETE FROM `phones_counter` WHERE (`phone`='" & phone & "')", oDBConn)
                rc.ExecuteNonQuery()
                rc.Dispose()
            End If
        Catch ex As Exception
            MsgBox("Ошибка DBEstate_AddEstate: " & ex.Message)
        End Try
    End Sub

    'Добавляет массив телефонов типа Array в таблицу риэлторов
    Public Sub DBEstate_AddEstate(ByRef phones() As String)
        For i As Integer = 0 To UBound(phones)
            DBEstate_AddEstate(phones(i))
        Next
    End Sub

    'Добавляет массив телефонов типа ArrayList в таблицу риэлторов
    Public Sub DBEstate_AddEstate(ByRef phones As ArrayList)
        For i As Integer = 0 To phones.Count - 1
            DBEstate_AddEstate(phones(i).ToString())
        Next
    End Sub




    '===========================================
    '=== Работа с таблицей старых объявлений ===
    '===========================================

    'Возвращает true если объявление старое, иначе false
    Public Function DBAdverts_isOldAdvert(ByVal advert As String, ByVal advert_id As String) As Boolean
        Try
            ConnectDB() 'подключаемся к базе данных
            Dim rc As OleDb.OleDbCommand
            rc = New OleDb.OleDbCommand("SELECT id FROM `estate_adverts` WHERE advert='" & advert & "' AND advid='" & advert_id & "'", oDBConn)
            If rc.ExecuteScalar() Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            MsgBox("Ошибка DBAdverts_isOldAdvert: " & ex.Message)
            Return True 'вернем true чтобы избежать дальнейшего добавления в БД
        End Try
    End Function


    'Добавляет в таблицу старых объявлений новую объяву
    Public Sub DBAdverts_addOldAdvert(ByVal advert As String, ByVal advert_id As String)
        Try
            ConnectDB() 'подключаемся к базе данных
            Dim rc As OleDb.OleDbCommand
            rc = New OleDb.OleDbCommand("INSERT INTO `estate_adverts` (`advert`,`advid`) VALUES ('" & advert & "','" & advert_id & "')", oDBConn)
            rc.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Ошибка DBAdverts_addOldAdvert: " & ex.Message)
        End Try
    End Sub



    '===========================================
    '=== Работа с таблицей счетчик телефонов ===
    '===========================================

    Public Function DBCounter_Get(ByVal phone As String, ByRef adv_name As String) As Integer
        DBCounter_Get = 0
        Try
            phone = getSmallPhone(phone)
            ConnectDB() 'подключаемся к базе данных
            Dim rc As OleDb.OleDbCommand
            Dim rr As OleDb.OleDbDataReader
            rc = New OleDb.OleDbCommand("SELECT `count` FROM `phones_counter` " & _
                                        "WHERE `phone`='" & phone & "' AND `adv_name`='" & adv_name & "'", oDBConn)
            rr = rc.ExecuteReader
            If rr.HasRows = True Then
                rr.Read()
                Return CInt(rr.GetValue(0))
            Else
                Return 0
            End If
        Catch ex As Exception
            MsgBox("Ошибка DBCounter_Get: " & ex.Message)
        End Try
    End Function


    Public Sub DBCounter_Inc(ByVal phone As String, ByRef adv_name As String)
        Try
            phone = getSmallPhone(phone)
            Dim count As Integer = DBCounter_Get(phone, adv_name)
            Dim rc As OleDb.OleDbCommand
            ConnectDB() 'подключаемся к базе данных
            count += 1
            If count = 1 Then
                rc = New OleDb.OleDbCommand("INSERT INTO `phones_counter` (`phone`, `count`, `adv_name`) " & _
                                            "VALUES ('" & phone & "','" & count & "','" & adv_name & "')", oDBConn)
            Else
                rc = New OleDb.OleDbCommand("UPDATE `phones_counter` SET `count`='" & count & "' " & _
                                            "WHERE (`phone`='" & phone & "' AND `adv_name`='" & adv_name & "')", oDBConn)
            End If
            rc.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Ошибка DBCounter_Inc: " & ex.Message)
        End Try
    End Sub
End Module